package br.com.fiap.mercadinho.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import br.com.fiap.mercadinho.bean.Product;
import br.com.fiap.mercadinho.bean.Stock;
import br.com.fiap.mercadinho.common.DAOException;
import br.com.fiap.mercadinho.common.GenericDAOException;

public class StockDAO extends GenericDAO{

	private static final String FIND_ALL_STOCK = " SELECT STOCK_ID,QUANTITY,PRODUCT_ID FROM STOCK ";
	
	private static final String FIND_STOCK_BY_ID = " SELECT STOCK_ID,QUANTITY,PRODUCT_ID FROM STOCK WHERE STOCK_ID = ? ";
	
	private static final String UPDATE_STOCK = " UPDATE STOCK SET QUANTITY = ? WHERE STOCK_ID = ? ";
	
	private static final String INSERT_STOCK = " INSERT INTO STOCK (STOCK_ID,QUANTITY,PRODUCT_ID) VALUES (0,?,?) ";
	
	private static final String UPDATE_PRODUCT_QUANTITY = "UPDATE STOCK SET QUANTITY = QUANTITY - ? WHERE PRODUCT_ID = ? ";
	
	public List<Stock> findAllStockProducts() throws DAOException {
		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(FIND_ALL_STOCK);
			ResultSet rs = pst.executeQuery();
			List<Stock> listStockProducts = new ArrayList<>();

			while (rs.next()) {
				listStockProducts.add(buildStockProduct(rs));
			}
			return listStockProducts;
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in find product method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}
	
	public Map<Integer, Stock> getStockProductsMap() throws DAOException{
		
		List<Stock> list = findAllStockProducts();
		Map<Integer, Stock> stockMap = new HashMap<Integer, Stock>();
		for (Iterator<Stock> iterator = list.iterator(); iterator.hasNext();) {
			Stock stock =  iterator.next();
			
			stockMap.put(stock.getProductId(), stock);
		}
		
		return stockMap;
		
	}
	
	public Stock findStockProductById(Integer idStock) throws DAOException {
		try {

			openConnection();
			PreparedStatement pst = conn.prepareStatement(FIND_STOCK_BY_ID);
			pst.setInt(1, idStock);
			ResultSet rs = pst.executeQuery();

			if (rs.next())
				return buildStockProduct(rs);

		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in find product method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
		return null;
	}
	
	public void updateProductStock(Stock stock) throws DAOException {
		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(UPDATE_STOCK);
			pst.setInt(1, stock.getQuantity());
			pst.setInt(2, stock.getId());
			pst.executeUpdate();
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in update stock method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}
	
	public void updateProductsQuantity(Map<Integer, Product> products) throws DAOException {
		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(UPDATE_PRODUCT_QUANTITY);
			
			Iterator<Product> productInterator = products.values().iterator();
			Product prod;
			while (productInterator.hasNext()){
				prod = productInterator.next();
				pst.setInt(1, prod.getQuantity());
				pst.setInt(2, prod.getId());
				pst.executeUpdate();
			}
			
			
			
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in update stock method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}

	public void saveStockProduct(Stock stock) throws DAOException {
		try {
			openConnection();
			PreparedStatement pst = conn.prepareStatement(INSERT_STOCK);
			pst.setInt(1, stock.getQuantity());
			pst.setInt(2, stock.getProduct().getId());
			pst.executeUpdate();
		} catch (GenericDAOException | SQLException e) {
			throw new DAOException("Error in insert stock method: "
					+ e.getMessage());
		} finally {
			try {
				closeConnction();
			} catch (GenericDAOException e) {
				throw new DAOException("Error in close connection method: "
						+ e.getMessage());
			}
		}
	}
	
	
	private Stock buildStockProduct(ResultSet rs) throws DAOException {
		Stock stock = new Stock();
		ProductDAO dao = new ProductDAO();
		try {
			stock.setId(rs.getInt("STOCK_ID"));
			stock.setQuantity(rs.getInt("QUANTITY"));
			stock.setProduct(dao.findProductById(rs.getInt("PRODUCT_ID")));
			stock.getProduct().setQuantity(stock.getQuantity());
			return stock;
		} catch (SQLException e) {
			throw new DAOException("Error in build stock method: "
					+ e.getMessage());
		}

	}
	

	
}
